﻿/*
    File name:          MSSQL.cs
    File type:          Class
    Author:             Jakob Sulby 09136
    Creation date:      24-03-2011
    Last modified:      16-05-2011
    Last modified by:   Jakob Sulby 09136
    Description:        Class that implements the abstract Database.
*/

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using System.Windows;

namespace i4pro_gruppe_d.Database
{
    using Types;
    public class MSSQL:AbstractDatabase
    {
        private int numberOfBriks = 0;
        private const string ConnectionInfo = "Data Source=webhotel10.iha.dk;Initial Catalog=F11I4PRJ4GRD;Persist Security Info=True;User ID=F11I4PRJ4GRD;Password=F11I4PRJ4GRD";       

        #region Robot
        public override bool WriteRobot(Robot robot)
        {
            bool completeState = true;
            SqlConnection conn;
            conn = new SqlConnection(ConnectionInfo);
            string insertString = "";

            try
            {
                // Open the connection
                conn.Open();
                string posb = robot.PositionBefore[0] + "," + robot.PositionBefore[1] + "," + robot.PositionBefore[2] +  "," + robot.PositionBefore[3] +  "," + robot.PositionBefore[4];
                string posa = robot.PositionAfter[0] + "," + robot.PositionAfter[1] + "," + robot.PositionAfter[2] + "," + robot.PositionAfter[3] + "," + robot.PositionAfter[4];

                // Prepare command string
                insertString = @"INSERT INTO RobotLog
                (_PositionBefore ,_PositionAfter, _GripSpanBefore, _GripSpanAfter, _Time, _BatchID)
                VALUES  ('" + posb + "', '" + posa + "','" + robot.GripSpanBefore + "','" + robot.GripSpanAfter + "','" + DateTime.Now +"','" + GetCurrentBatch() + "')";

                // 1. Instantiate a new command with a query and connection
                SqlCommand cmd = new SqlCommand(insertString, conn);

                // 2. Call ExecuteNonQuery to send command
                cmd.ExecuteNonQuery();

            }

            catch(Exception)
            {
                completeState = false;
                Console.WriteLine("Kunne ikke uploade robot." + insertString);
            }

            finally
            {
                // Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }

            return completeState;
        }
        #endregion

        #region Log

        public override List<Log> ReadLog(DateTime dateTime)
        {
            List<Log> returnLog = new List<Log>();
            List<Log> tempReturnLog = new List<Log>();
            SqlConnection conn;
            conn = new SqlConnection(ConnectionInfo);
            SqlDataReader reader = null;

            string date = dateTime.ToString("dd-MM-yyyy");

            #region Robot
            try
            {
                // Open the connection
                conn.Open();

                // Prepare command string
                string selectString =
                    @"SELECT _PositionBefore, _PositionAfter, _GripSpanBefore, _GripSpanAfter, _BatchID, _Time
                    FROM RobotLog
                    WHERE _Time LIKE '" + date + "%'";


                // 1. Instantiate a new command with a query and connection
                SqlCommand cmd = new SqlCommand(selectString, conn);

                // 2. Call ExecuteNonQuery to send command
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Log tempLog = new Log();
                    tempLog.LogTime = DateTime.Parse(reader["_Time"].ToString());
                    tempLog.BatchId = (int)reader["_BatchID"];
                    tempLog.LogType = (int)Codes.Normal;
                    tempLog.LogEntry = "Moved from: " + reader["_PositionBefore"] + ", and gripspan: " + reader["_GripSpanBefore"]
                                                + " Moved to: " + reader["_PositionAfter"] + " and gripspan: " + reader["_GripSpanAfter"] + ".";
                    returnLog.Add(tempLog);
                }
            }

            catch (Exception)
            {
                
            }

            finally
            {
                // Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }
            #endregion

            #region EventLog
            try
            {
                // Open the connection
                conn.Open();

                // Prepare command string
                string selectString =
                    @"SELECT _Type, _Description, _BatchID, _Time
                    FROM EventLog
                    WHERE _Time LIKE '" + date + "%'";


                // 1. Instantiate a new command with a query and connection
                SqlCommand cmd = new SqlCommand(selectString, conn);

                // 2. Call ExecuteNonQuery to send command
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Log tempLog = new Log();
                    tempLog.LogTime = DateTime.Parse(reader["_Time"].ToString());
                    tempLog.BatchId = (int)reader["_BatchID"];
                    tempLog.LogType = (int)reader["_Type"];
                    tempLog.LogEntry = reader["_Description"].ToString();
                    returnLog.Add(tempLog);
                }
            }
            catch (Exception)
            {
                
            }

            finally
            {
                // Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }
            #endregion

            #region Script
            try
            {
                // Open the connection
                conn.Open();

                // Prepare command string
                string selectString =
                    @"SELECT _Name, _BatchId, _Time
                    FROM ScriptLog
                    WHERE _Time LIKE '" + date + "%'";

                // 1. Instantiate a new command with a query and connection
                SqlCommand cmd = new SqlCommand(selectString, conn);

                // 2. Call ExecuteNonQuery to send command
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Log tempLog = new Log();
                    tempLog.LogTime = DateTime.Parse(reader["_Time"].ToString());
                    tempLog.BatchId = (int)reader["_BatchID"];
                    tempLog.LogType = (int)Codes.Normal;
                    tempLog.LogEntry = "Saved the script: " + reader["_Name"];
                    returnLog.Add(tempLog);
                }
            }
            catch (Exception)
            {
                
            }

            finally
            {
                // Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }
            #endregion

            #region Batch
            try
            {
                // Open the connection
                conn.Open();

                // Prepare command string
                string selectString =
                    @"SELECT _FinishTime, _NumberInProduction
                    FROM BatchLog
                    WHERE _StartTime LIKE '" + date + "%'";


                // 1. Instantiate a new command with a query and connection
                SqlCommand cmd = new SqlCommand(selectString, conn);

                // 2. Call ExecuteNonQuery to send command
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Log tempLog = new Log();
                    tempLog.LogTime = DateTime.Parse(reader["_StartTime"].ToString());
                    tempLog.LogEntry = "Sequence stopped at " + DateTime.Parse(reader["_FinishTime"].ToString()).ToString("HH:mm:ss") + " , " + reader["_NumberInProduction"] + " bricks were sorted.";
                    tempLog.LogType = (int)Codes.Command;
                    returnLog.Add(tempLog);
                }
            }
            catch (Exception)
            {
                
            }

            finally
            {
                // Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }
            #endregion

            int oldIndex = 0, newIndex = 0;
            bool isFinished = false;

            //Sorterer Z-A
            for (int k = 0; k < 2; k++ )
            {
                for (int i = 1; i < returnLog.Count; i++)
                {
                    if (returnLog[i].LogTime < returnLog[i - 1].LogTime)
                    {
                        var item = returnLog[i];
                        oldIndex = i;
                        newIndex = i - 1;
                        returnLog.RemoveAt(oldIndex);
                        returnLog.Insert(newIndex, item);

                        i = 1;
                    }
                }
            }

            //Sorter A-Z
            for (int i = returnLog.Count - 1, j = 0; returnLog.Count != tempReturnLog.Count; i--, j++)
            {
                tempReturnLog.Insert(j, returnLog[i]);
            }

            return tempReturnLog;
        }

        public override bool WriteEventLog(EventLog eventLog)
        {
            bool completeState = false;
            SqlConnection conn;
            conn = new SqlConnection(ConnectionInfo);

            try
            {
                // Open the connection
                conn.Open();

                // Prepare command string
                string insertString = @"INSERT INTO EventLog
                    (_Type ,_Description, _Time ,_BatchID)
                    VALUES ('" + eventLog.LogType + "',  '" + eventLog.LogEntry + "', '" + eventLog.LogTime + "', '" + GetCurrentBatch() + "')";

                // 1. Instantiate a new command with a query and connection
                SqlCommand cmd = new SqlCommand(insertString, conn);

                // 2. Call ExecuteNonQuery to send command
                cmd.ExecuteNonQuery();

                // Set to completed
                completeState = true;
            }
            catch (Exception)
            {
            }

            finally
            {
                // Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }

            return completeState;
        }
        #endregion

        #region User
        public override bool NewUser(User user)
        {
            

            bool completeState = false;
            SqlConnection conn;
            conn = new SqlConnection(ConnectionInfo);

            try
            {
                // Open the connection
                conn.Open();

                // Prepare command string
                string insertString = @"INSERT INTO LogIn
                    (_ID ,_Password ,_UserType, _Time)
                    VALUES ('"+ user.UserID +"', '"+ user.Password +"', '"+ user.UserType +"', '" + DateTime.Now +"')";

                // 1. Instantiate a new command with a query and connection
                SqlCommand cmd = new SqlCommand(insertString, conn);

                // 2. Call ExecuteNonQuery to send command
                cmd.ExecuteNonQuery();

                // Set to completed
                completeState = true;
            }

            catch(Exception e)
            {
                string message = "Can't create user!\n\nDescription:\n" + e.Message;
                MessageBox.Show(message);
                completeState = false;
            }

            finally
            {
                // Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return completeState;
        }
        
        public override int GetUserRights(User user) //If returns -1, the user doesn't exist or wrong password.
        {
            int userType = -1;
            SqlConnection conn;
            conn = new SqlConnection(ConnectionInfo);
            SqlDataReader reader = null;

            try
            {
                // Open the connection
                conn.Open();

                // Prepare command string
                string selectString = @"SELECT _UserType
                    FROM LogIn
                    WHERE _ID LIKE '" + user.UserID + "' AND _Password LIKE '" + user.Password + "'";

                // 1. Instantiate a new command with a query and connection
                SqlCommand cmd = new SqlCommand(selectString, conn);

                // 2. Call ExecuteNonQuery to send command
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    userType = (int)reader["_UserType"];
                }
            }

            catch(Exception)
            {
                userType = -1;
            }

            finally
            {
                // Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }

            return userType;
        }

        public override bool UpdateUser(User user, String oldUserName)
        {
            bool completeState = true;
            SqlConnection conn;
            conn = new SqlConnection(ConnectionInfo);

            if(user.Password == "")
            {
                try
                {
                    // Open the connection
                    conn.Open();

                    // Prepare command string
                    string updateString = @"UPDATE LogIn
                        SET _UserType = '"+user.UserType+@"', _ID = '"+user.UserID+@"', _Time = '"+DateTime.Now+@"'
                        WHERE _ID = '"+oldUserName+"'";

                    // 1. Instantiate a new command with a query and connection
                    SqlCommand cmd = new SqlCommand(updateString, conn);

                    // 2. Call ExecuteNonQuery to send command
                    cmd.ExecuteNonQuery();
                }

                catch (Exception)
                {
                    completeState = false;
                }

                finally
                {
                    // Close the connection
                    if (conn != null)
                    {
                        conn.Close();
                    }
                }

                return completeState;
            }
            try
            {
                // Open the connection
                conn.Open();

                // Prepare command string
                string updateString = @"UPDATE LogIn
                    SET _UserType = '" + user.UserType + @"', _ID = '" + user.UserID + @"', _Time = '" + DateTime.Now + @"', _Password ='"+user.Password+@"'
                    WHERE _ID = '" + oldUserName + "'";

                // 1. Instantiate a new command with a query and connection
                SqlCommand cmd = new SqlCommand(updateString, conn);

                // 2. Call ExecuteNonQuery to send command
                cmd.ExecuteNonQuery();
            }

            catch (Exception)
            {
                completeState = false;
            }

            finally
            {
                // Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }

            return completeState;

            
        }

        public override List<User> getUserList()
        {
            SqlConnection conn;
            conn = new SqlConnection(ConnectionInfo);
            SqlDataReader reader = null;
            List<User> userList = new List<User>();
            

            try
            {
                // Open the connection
                conn.Open();

                // Prepare command string
                string selectString = @"SELECT _ID, _UserType
                    FROM LogIn
                    ORDER BY _ID";

                // 1. Instantiate a new command with a query and connection
                SqlCommand cmd = new SqlCommand(selectString, conn);

                // 2. Call ExecuteNonQuery to send command
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    User tempUser = new User();
                    tempUser.UserID = reader["_ID"].ToString();
                    tempUser.UserType = (int)reader["_UserType"];

                    userList.Add(tempUser);
                }
            }

            catch (Exception)
            {
                userList = new List<User>();
            }

            finally
            {
                // Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }

            return userList;
        }

        public override bool DeleteUser(User user)
        {
            bool completeState = true;
            SqlConnection conn;
            conn = new SqlConnection(ConnectionInfo);

            try
            {
                // Open the connection
                conn.Open();

                // Prepare command string
                string selectString = @"DELETE FROM LogIn
                WHERE _ID = '" + user.UserID + "'";

                // 1. Instantiate a new command with a query and connection
                SqlCommand cmd = new SqlCommand(selectString, conn);

                // 2. Call ExecuteNonQuery to send command
                cmd.ExecuteNonQuery();
            }

            catch (Exception)
            {
                completeState = false;
            }

            finally
            {
                // Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return completeState;
        }

        #endregion

        #region Brick
        public override bool WriteBrick(Brick brick)
        {
            

            bool completeState = true;
            SqlConnection conn;
            conn = new SqlConnection(ConnectionInfo);
            
            try
            {
                // Open the connection
                conn.Open();

                // Prepare command string
                string insertString = @"
                    insert into BrickLog
                    (_Height, _Width, _Length, _Weight, _Time , _BatchID)
                    values ('" + brick.Height + "', '" + brick.Width + "', '" + brick.Length + "', '" + brick.Weight + "', '" + brick.Time + "', '" + GetCurrentBatch() + "')";

                // 1. Instantiate a new command with a query and connection
                SqlCommand cmd = new SqlCommand(insertString, conn);

                // 2. Call ExecuteNonQuery to send command
                cmd.ExecuteNonQuery();
            }

            catch(Exception)
            {
                completeState = false;
            }

            finally
            {
                // Close the connection
                if (conn != null)
                {
                    conn.Close();
                    SetNumberOfBricks(++numberOfBriks);
                }
            }
            return completeState;
        }
        public override List<Brick> ReadBrick(DateTime dateTime)
        {
            List<Brick> returnBrick = new List<Brick>();
            SqlConnection conn;
            conn = new SqlConnection(ConnectionInfo);
            SqlDataReader reader = null;
            
            try
            {
                // Open the connection
                conn.Open();

                // Prepare command string
                string selectString = @"SELECT _Height, _Width, _Length, _Weight, _Time 
                FROM BrickLog
                WHERE _Time LIKE '"+ dateTime.ToString("dd-MM-yyyy") +"%'";

                // 1. Instantiate a new command with a query and connection
                SqlCommand cmd = new SqlCommand(selectString, conn);

                // 2. Call ExecuteNonQuery to send command
                reader = cmd.ExecuteReader();

               while (reader.Read())
               {
                   //int height, int width, int length, int weight
                   returnBrick.Add(
                       new Brick(
                           DateTime.Parse(reader["_Time"].ToString()),
                           (int)reader["_Height"],
                           (int)reader["_Width"],
                           (int)reader["_Length"],
                           (int)reader["_Weight"]));
               }
            }

            catch(Exception)
            {
                returnBrick = new List<Brick>();
            }

            finally
            {
                // Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return returnBrick;
        }
        public override bool UpdateBrick(Brick brick)
        {
            bool completeState = true;
            SqlConnection conn;
            conn = new SqlConnection(ConnectionInfo);

            try
            {
                // Open the connection
                conn.Open();

                // Prepare command string
                string insertString = @"UPDATE BrickLog
                SET _Height = '"+ brick.Height +@"'
                ,_Width = '"+ brick.Width +@"'
                ,_Length = '" + brick.Length + @"'
                ,_Weight = '" + brick.Weight + @"'
                WHERE _Time = '" + brick.Time + "'";

                // 1. Instantiate a new command with a query and connection
                SqlCommand cmd = new SqlCommand(insertString, conn);

                // 2. Call ExecuteNonQuery to send command
                cmd.ExecuteNonQuery();
            }

            catch(Exception)
            {
                completeState = false;
            }

            finally
            {
                // Close the connection
                if (conn != null)
                {
                    conn.Close();
                    completeState = true;
                    SetNumberOfBricks(++numberOfBriks);
                }
            }
            return completeState;
        }
        #endregion

        #region Script
        public override List<string> ReadScriptNames()
        {
            List<string> returnNames = new List<string>();
            SqlConnection conn;
            conn = new SqlConnection(ConnectionInfo);
            SqlDataReader reader = null;

            try
            {
                // Open the connection
                conn.Open();

                // Prepare command string
                string selectString = @"SELECT _Name
                FROM ScriptLog";

                // 1. Instantiate a new command with a query and connection
                SqlCommand cmd = new SqlCommand(selectString, conn);

                // 2. Call ExecuteNonQuery to send command
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    //int height, int width, int length, int weight
                    returnNames.Add(reader["_Name"].ToString());
                }
            }

            catch(Exception)
            {
                returnNames = new List<string>();
            }

            finally
            {
                // Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return returnNames;
        }

        public override Script ReadScript(string scriptName)
        {
            Script script = new Script();
            SqlConnection conn;
            conn = new SqlConnection(ConnectionInfo);
            SqlDataReader reader = null;

            try
            {
                // Open the connection
                conn.Open();

                // Prepare command string
                string selectString = @"SELECT _Name, _Script
                FROM ScriptLog
                WHERE _NAME = '"+ scriptName +"'";

                // 1. Instantiate a new command with a query and connection
                SqlCommand cmd = new SqlCommand(selectString, conn);

                // 2. Call ExecuteNonQuery to send command
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    //int height, int width, int length, int weight
                    script = new Script(reader["_Name"].ToString(),
                    Encoding.ASCII.GetString((byte[])reader["_Script"]));
                }
            }

            catch(Exception)
            {
                script = new Script();
            }

            finally
            {
                // Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return script;
        }

        public override bool WriteScript(Script script)
        {
            bool retval = true;
            SqlConnection conn;
            conn = new SqlConnection(ConnectionInfo);

            try
            {
                // Open the connection
                conn.Open();

                // Prepare command string
                string insertString = @"
                    IF EXISTS (SELECT _Name FROM ScriptLog WHERE _Name = '"+ script.Name + @"')
	                    UPDATE ScriptLog SET _Script = '" +script.CodeLines+ @"', _BatchID = '"+GetCurrentBatch()+@"', _Time = '"+DateTime.Now+@"' WHERE _Name = '"+script.Name+@"'
                    ELSE
	                    INSERT INTO ScriptLog (_Name, _Script, _BatchID, _Time) VALUES ('"+script.Name+"', '"+script.CodeLines+"', '"+GetCurrentBatch()+"', '"+DateTime.Now+"')";

                // 1. Instantiate a new command with a query and connection
                    SqlCommand cmd = new SqlCommand(insertString, conn);

                // 2. Call ExecuteNonQuery to send command
                cmd.ExecuteNonQuery();
            }

            catch(Exception)
            {
                retval = false;
            }

            finally
            {
                // Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return retval;
        }

        public override bool RemoveScript(string script)
        {
            bool retval = true;
            SqlConnection conn;
            conn = new SqlConnection(ConnectionInfo);

            try
            {
                // Open the connection
                conn.Open();

                // Prepare command string
                string selectString = @"DELETE FROM ScriptLog
                WHERE _NAME = '" + script + "'";

                // 1. Instantiate a new command with a query and connection
                SqlCommand cmd = new SqlCommand(selectString, conn);

                // 2. Call ExecuteNonQuery to send command
                cmd.ExecuteNonQuery();
            }

            catch(Exception)
            {
                retval = false;
            }

            finally
            {
                // Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return retval;
        }

        #endregion
        
        #region Batch
        public override bool NewBatch(DateTime startTime, DateTime finishTime)
        {
            bool completeState = true;
            SqlConnection conn;
            conn = new SqlConnection(ConnectionInfo);

            try
            {
                // Open the connection
                conn.Open();

                // Prepare command string
                string insertString = @"
                    INSERT INTO BatchLog
                    (_StartTime, _FinishTime, _NumberInProduction)
                    VALUES ('" + startTime + "', '" + finishTime + "', '" + 0 + "')";

                // 1. Instantiate a new command with a query and connection
                SqlCommand cmd = new SqlCommand(insertString, conn);

                // 2. Call ExecuteNonQuery to send command
                cmd.ExecuteNonQuery();
            }

            catch(Exception)
            {
                completeState = false;
            }

            finally
            {
                // Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }
            numberOfBriks = new int();
            numberOfBriks = 0;

            return completeState;
        }

        public override bool SetBatchEndTime(DateTime finishTime)
        {
            bool completeState = true;
            SqlConnection conn;
            conn = new SqlConnection(ConnectionInfo);

            try
            {
                // Open the connection
                conn.Open();

                // Prepare command string
                string updateString = @"UPDATE BatchLog 
                SET _FinishTime = '"+ finishTime + @"'
                WHERE _BatchID = '" + GetCurrentBatch() + "'";

                // 1. Instantiate a new command with a query and connection
                SqlCommand cmd = new SqlCommand(updateString, conn);

                // 2. Call ExecuteNonQuery to send command
                cmd.ExecuteNonQuery();
            }

            catch(Exception)
            {
                completeState = false;
            }

            finally
            {
                // Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }

            return completeState;
        }

        private bool SetNumberOfBricks(int _number)
        {
            bool completeState = true;
            SqlConnection conn;
            conn = new SqlConnection(ConnectionInfo);

            try
            {
                // Open the connection
                conn.Open();

                // Prepare command string
                string updateString = @"UPDATE BatchLog 
                SET _NumberInProduction = '" + _number + @"'
                WHERE _BatchID = '" + GetCurrentBatch() + "'";

                // 1. Instantiate a new command with a query and connection
                SqlCommand cmd = new SqlCommand(updateString, conn);

                // 2. Call ExecuteNonQuery to send command
                cmd.ExecuteNonQuery();
            }

            catch(Exception)
            {
                completeState = false;
            }

            finally
            {
                // Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }

            return completeState;
        }

        private string GetCurrentBatch()
        {
            SqlConnection conn;
            conn = new SqlConnection(ConnectionInfo);
            string returnVal = "";

            try
            {
                // Open the connection
                conn.Open();

                // Prepare command string
                string selectString = @"SELECT TOP 1 _BatchID
                FROM BatchLog
                ORDER BY _BatchID DESC";

                // 1. Instantiate a new command with a query and connection
                SqlCommand cmd = new SqlCommand(selectString, conn);

                // 2. Call ExecuteNonQuery to send command
                var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    returnVal = String.Format("{0}", reader[0]);
                }
            }

            catch(Exception)
            {
                returnVal = "";
            }

            finally
            {
                // Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
                
            }
            return returnVal;
        }
        #endregion
    }
}
